--/
CREATE PROCEDURE sp_getPublicRecursiveBom
        @materielCode nvarchar(50),--前台穿的物料编码
        @bomTopCode nvarchar(50),--java 传的母件Code
        --@myindex int,--用作判断第一条数据是否是母件
        @mark nvarchar(50)

AS

/* Procedure body */
        Create table #moreRankTable(
                reorder int,
                rank nvarchar(50),
                bomlevel int,--阶次
                bomCode nvarchar(50),--bom编号
                pCode nvarchar(50),--bom上级物料
                materielCode nvarchar(50),--物料编号
                materielName nvarchar(50),--物料名称
                amount  nvarchar(10), --数量
                attritionRate nvarchar(50),--损耗率
                fixedAttritionRate nvarchar(50),--固定损耗量
                effectiveDate nvarchar(50),--生效日期
                expiryDate nvarchar(50),--失效日期
                processName nvarchar(50),--工艺路线
                processVersion nvarchar(50),--工艺版本
                materialProcess nvarchar(50),--发料工序
                auditStatusName nvarchar(50),--审核状态
                clienFeed nvarchar(50),--是否供应物料
                manufacturer nvarchar(50),--制造厂商
                flag int 
        )
         Create table #singRankTable(
                pCode nvarchar(50),--bom上级物料
                materielCode nvarchar(50),--物料编号
                materielName nvarchar(50),--物料名称
                amount  nvarchar(10), --数量
                attritionRate nvarchar(50),--损耗率
                fixedAttritionRate nvarchar(50),--固定损耗量
                effectiveDate nvarchar(50),--生效日期
                expiryDate nvarchar(50),--失效日期
                processName nvarchar(50),--工艺路线
                processVersion nvarchar(50),--工艺版本
                materialProcess nvarchar(50),--发料工序
                auditStatusName nvarchar(50),--审核状态
                clienFeed nvarchar(50),--是否供应物料
                manufacturer nvarchar(50),--制造厂商
                flag int 
        )
       
         declare @bomCode nvarchar(50)--声明一个Bom编码
         declare @mer nvarchar(50)
         declare @level int
         set @level='0'
         declare @@reorder int
         set @@reorder = '1'
         
select @mer =s.materielCode from (select  materielCode from s_bomTop_info where status='1' and bomTopCode =@bomTopCode) s 
         
         
IF (@mark='sing')
        begin
        
        IF (@materielCode=''Or @materielCode=@mer)
                begin 
                        --第一条数据为母件
                         select  @bomCode=@bomTopCode
                         print  '母件的bomCode:'+@bomCode
                         --insert into #singRankTable select  s.rank,@level,s.bomCode,s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from (select '0' as rank,bomTopCode as bomCode,bomTopCode as pCode,materielCode,materielName,'1' as amount,'' as attritionRate,'' as fixedAttritionRate,effectiveDate,expiryDate,processName,'' as processVersion,'' as materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,'' as manufacturer from s_bomTop_info where status='1' and bomTopCode=@bomCode) s
                 end
         else
                 begin
                        --第一条数据为非母件
                      select  @bomCode=s.bomCode
                      from (select top 1 bomCode from s_bom_info where status='1' and materielCode=@materielCode and bomTopCode =@bomTopCode) s 
                      --insert into #singRankTable select s.rank,@level,s.bomCode,s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from (select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,bomCode,pCode,materielCode,materielName, amount,attritionRate,fixedAttritionRate,effectiveDate,expiryDate,processName,processVersion, materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,manufacturer from s_bom_info where status='1' and bomCode=@bomCode and bomTopCode =@bomTopCode) s 
                 end 
         declare scoll_bom cursor for select guid from s_bom_info where status='1' and pCode=@bomCode and bomTopCode =@bomTopCode
         open scoll_bom
         declare @id varchar(50)   
         fetch next from scoll_bom into @id  
         while @@fetch_status=0  
                 begin  
                         --select @id   
                         print @id 
                         insert into #singRankTable select s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from (select bomCode,pCode,materielCode,materielName, amount,attritionRate,fixedAttritionRate,effectiveDate,expiryDate,processName,processVersion, materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,manufacturer from s_bom_info where status='1' and guid=@id) s 
                         fetch next from scoll_bom into @id    
                 end     
         close scoll_bom    
         deallocate scoll_bom 
         select * from #singRankTable 
        end
else
begin 
 IF (@materielCode=''Or @materielCode=@mer)
 begin 
        --第一条数据为母件
         select  @bomCode=@bomTopCode
         --from (select bomTopCode from s_bomTop_info where status='1' and materielCode=@materielCode) s 
         
         select  @level='0'
         print  '母件的bomCode:'+@bomCode
         insert into #moreRankTable select  @@reorder,s.rank,@level,s.bomCode,s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from (select '0' as rank,bomTopCode as bomCode,'0' as pCode,materielCode,materielName,'1' as amount,'' as attritionRate,'' as fixedAttritionRate,effectiveDate,expiryDate,processName,'' as processVersion,'' as materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,'' as manufacturer from s_bomTop_info where status='1' and bomTopCode=@bomCode) s
 end
 else
         begin
                --第一条数据为非母件
              select  @bomCode=s.bomCode,@level=s.rank
              from (select top 1 bomCode,rank from s_bom_info where status='1' and materielCode=@materielCode and bomTopCode =@bomTopCode) s 
              --insert into #moreRankTable select @@reorder,s.rank,@level,s.bomCode,s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from (select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,bomCode,pCode,materielCode,materielName, amount,attritionRate,fixedAttritionRate,effectiveDate,expiryDate,processName,processVersion, materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,manufacturer from s_bom_info where status='1' and bomCode=@bomCode and bomTopCode =@bomTopCode) s 
              /*作为母件数量默认为1*/
              insert into #moreRankTable select @@reorder,s.rank,@level,s.bomCode,s.pCode,s.materielCode,s.materielName,1 as amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from (select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,bomCode,pCode,materielCode,materielName, amount,attritionRate,fixedAttritionRate,effectiveDate,expiryDate,processName,processVersion, materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,manufacturer from s_bom_info where status='1' and bomCode=@bomCode and bomTopCode =@bomTopCode) s 
         end  
--判断临时表的数据是否还有下一阶
while exists (select * from #moreRankTable where  #moreRankTable.flag !=0)
       
        begin 
        set @@reorder = @@reorder+1
                if exists (select * from  #moreRankTable  where bomlevel = @level and flag!=0)
                 --如果存在下一级，将查着的数据接着添加到临时表中接着查
                        begin
                                set rowcount 1
                                insert into #moreRankTable select @@reorder,s.rank,@level+1,s.bomCode,s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from( select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,bomCode,pCode,materielCode,materielName, amount,attritionRate,fixedAttritionRate,effectiveDate,expiryDate,processName,processVersion, materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,manufacturer from s_bom_info WHERE  status = '1' and bomTopCode = @bomTopCode) s 
                                inner join #moreRankTable on #moreRankTable.bomCode = s.pCode
                                where #moreRankTable.bomlevel =@level 
                                and s.bomCode not in (select #moreRankTable.bomCode from #moreRankTable where bomlevel=@level )and flag!=0 
                               
                                set rowcount 0
                                print 'child node'
                                set @level =@level +1
                                print @level
                        end 
                else if exists(
                        select s.pCode from s_bom_info s  inner join #moreRankTable on #moreRankTable.bomCode = s.pCode 
                        where #moreRankTable.bomlevel=@level-2 and s.status = '1' and s.bomTopCode = @bomTopCode
                        and s.bomCode not in (select #moreRankTable.bomCode from #moreRankTable where bomlevel=@level-1 ))
                         
                        begin
                                set rowcount 1
                                insert into #moreRankTable select @@reorder,s.rank,@level-1,s.bomCode,s.pCode,s.materielCode,s.materielName,s.amount,s.attritionRate,s.fixedAttritionRate,s.effectiveDate,s.expiryDate,s.processName,s.processVersion,s.materialProcess,s.auditStatusName,s.clienFeed,s.manufacturer,1 as flag from ( select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,bomCode,pCode,materielCode,materielName, amount,attritionRate,fixedAttritionRate,effectiveDate,expiryDate,processName,processVersion, materialProcess,auditStatusName,case clienFeed when '1' then '是' else '否' end as 'clienFeed' ,manufacturer from s_bom_info WHERE  status = '1' and bomTopCode = @bomTopCode) s  
                                inner join #moreRankTable on #moreRankTable.bomCode = s.pCode
                                where #moreRankTable.bomlevel =@level -2  and s.bomCode not in (select #moreRankTable.bomCode from #moreRankTable where bomlevel=@level-1 )
                               
                                set @level =@level -1
                                set rowcount 0
                                print 'brother node'
                        end
                else 
                        begin
                                update #moreRankTable set flag =0 where bomlevel =@level-1 and flag !=0  
                                
                                update #moreRankTable set flag =0 where bomlevel =@level-2 and flag !=0  
                                print 'return'
                                
                                set @level =@level -1
                        end 
        end
        
         select * from #moreRankTable  order by reorder desc
end
         
         
         
         
         
         
         
         
         
 

       

/
